﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Runda.EShop.Common.Utility;
using System.Text;
using Runda.EShop.Common.Entity;

namespace Runda.EShop.Dal
{
    public partial class BizCustomerQADal
    {
        public DataTable LoadCustomerMessageNonAnswer(List<SqlDbParameter> parms, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder(@"SELECT {0} A.MessageID,A.[CustomerID],A.CreatedAt,A.[Question],A.[AnswerBy],A.[AnswerByName],B.CustomerCode FROM Biz_Customer_QA A JOIN Biz_Customer B ON A.CustomerID=B.CustomerID WHERE A.Deleted = 0 {1}");
            string sqlStr = SqlDbParameter.BuildSqlString(sqlSb.ToString(), parms);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {1} ) AS RowNumber, * 
  FROM ({0}) as A
)
SELECT * from T WHERE RowNumber > @StartRowNum AND  RowNumber <= @EndRowNum
select count(*) from ({0}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, OrderBy);

            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, (PageIndex + 1) * PageSize);
            foreach (SqlDbParameter parameter in parms)
            {
                parameter.SetDbCommond(dbCommand);
            }

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public void ReplyCustomerMessageAnswer(BizCustomerQAEntity entity)
        {
            string sql = @"IF EXISTS (SELECT 1 FROM Biz_Customer_QA WHERE MessageID=@MessageID AND AnswerBy IS NULL)
UPDATE Biz_Customer_QA SET Answer=@Answer,AnswerBy=@AnswerBy,AnswerByName=@AnswerByName,CreatedAt=@CreatedAt,AnswerAt=@AnswerAt,LastModifyByName=@LastModifyByName,LastModifyAt=@LastModifyAt WHERE MessageID=@MessageID
ELSE
UPDATE Biz_Customer_QA SET Answer=@Answer,LastModifyByName=@LastModifyByName,LastModifyAt=@LastModifyAt WHERE MessageID=@MessageID
";

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@Answer", DbType.String, entity.Answer);
            CurrentDatabase.AddInParameter(dbcommand, "@AnswerBy", DbType.Guid, entity.AnswerBy);
            CurrentDatabase.AddInParameter(dbcommand, "@AnswerByName", DbType.String, entity.AnswerByName);
            CurrentDatabase.AddInParameter(dbcommand, "@CreatedAt", DbType.DateTime, entity.CreatedAt);
            CurrentDatabase.AddInParameter(dbcommand, "@AnswerAt", DbType.DateTime, entity.AnswerAt);
            CurrentDatabase.AddInParameter(dbcommand, "@LastModifyByName", DbType.String, entity.LastModifyByName);
            CurrentDatabase.AddInParameter(dbcommand, "@LastModifyAt", DbType.DateTime, entity.LastModifyAt);
            CurrentDatabase.AddInParameter(dbcommand, "@MessageID", DbType.Guid, entity.MessageID);

            CurrentDatabase.ExecuteNonQuery(dbcommand);
        }

        
    }
}
